﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;

namespace zvsWF
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            PrjProperty.PrjInit();
        }
        OleDbConnection mdbConn = new OleDbConnection();
        string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"data source = dbzayav1.mdb";

        /// <summary>
        /// Обовить таблицу
        /// </summary>
        private void RefreshTable()
        {

            mdbConn.ConnectionString = connString;
            mdbConn.Open();

            const string sqlZEnt = "SELECT zvs_entities.id AS ID, zvs_entities.ent_name AS EntName FROM zvs_entities order by zvs_entities.ent_name ";
            OleDbDataAdapter daZEnt = new OleDbDataAdapter(sqlZEnt, mdbConn);
            DataSet dsZEnt = new DataSet();
            daZEnt.Fill(dsZEnt);
            foreach (DataRow RowZEnt in dsZEnt.Tables[0].Rows)
            {
                PrjProperty.addTeml("Ent", new PrjTmpl()
                {
                    RowID = RowZEnt["ID"].ToString(),
                    Value = RowZEnt["EntName"].ToString()
                });
            }
            const string sqlZEmps = "SELECT zvs_emps.id AS ID, zvs_emps.emps_name AS EmpsName FROM zvs_emps order by zvs_emps.emps_name ";
            OleDbDataAdapter daZEmps = new OleDbDataAdapter(sqlZEmps, mdbConn);
            DataSet dsZEmps = new DataSet();
            daZEmps.Fill(dsZEmps);
            foreach (DataRow RowZEmps in dsZEmps.Tables[0].Rows)
            {
                PrjProperty.addTeml("Emps", new PrjTmpl()
                {
                    RowID = RowZEmps["ID"].ToString(),
                    Value = RowZEmps["EmpsName"].ToString()
                });
            }
            string schkEdate = "";
            if (checkBoxOnlyCurrent.Checked)
                schkEdate = "and zvs_zayav.edate is null";
            string schkEmps = "";
            if (checkBox1.Checked)
            {
                PrjTmpl prjtmpl = (PrjTmpl)comboBoxEmps.SelectedValue;

                schkEdate = String.Format(" and zvs_emps.id = {0} ", prjtmpl.RowID);
            }
            string sqlZVS = String.Format("SELECT zvs_zayav.id AS ID, zvs_zayav.sdate AS SDate, zvs_entities.ent_name AS EntName, "
                                + " zvs_zayav.subject AS Task, zvs_emps.emps_name AS EmpsName, zvs_zayav.edate AS EDate, "
                                + " zvs_entities.id AS EntID, zvs_emps.id AS EmpsID "
                                + " FROM zvs_zayav, zvs_entities, zvs_emps "
                                + " WHERE zvs_zayav.ent_id=zvs_entities.id AND zvs_zayav.emps_id=zvs_emps.id "
                                + " and ( (zvs_zayav.sdate BETWEEN #{0} {1}# and #{0} {2}#) or zvs_zayav.edate is null or ( zvs_zayav.edate BETWEEN #{0} {1}# and #{0} {2}#) ) "
                                + " {4} "
                                + " {3} order by zvs_zayav.sdate",
                                dateTimePickerFilter.Value.ToString("yyyy-MM-dd"),
                                "00:00", "23:55", schkEdate, schkEmps);

            OleDbDataAdapter daZVS = new OleDbDataAdapter(sqlZVS, mdbConn);
            DataSet dsZVS = new DataSet();
            daZVS.Fill(dsZVS);

            PrjProperty.listEvens = new List<ZEvent>();
            //Формирвание списка объектов - Работы
            foreach (DataRow RowZVS in dsZVS.Tables[0].Rows)
            {
                PrjProperty.listEvens.Add(new ZEvent()
                {
                    ZVSNO = RowZVS["ID"].ToString(),
                    ZVSTask = RowZVS["Task"].ToString(),
                    ZVSSDate = RowZVS["SDate"].ToString(),
                    ZVSEDate = RowZVS["EDate"].ToString(),
                    ZVSEnt = RowZVS["EntName"].ToString(),
                    ZVSEmps = RowZVS["EmpsName"].ToString(),
                    ZEntID = RowZVS["EntID"].ToString(),
                    ZEmpsID = RowZVS["EmpsID"].ToString()
                });
            }
            mdbConn.Close();


            if (dataGridView1.Rows.Count > 0 && dataGridView1.CurrentRow.Index > -1)
            {
                PrjProperty.currentTableIndex = dataGridView1.CurrentRow.Index;
            }
            dataGridView1.DataSource = null;
            dataGridView1.DataSource = PrjProperty.listEvens; // выводим в таблицу
            if (PrjProperty.currentTableIndex > -1)
            {
                //dataGridView1.SelectedRows = PrjProperty.currentTableIndex;
                try
                {
                    dataGridView1.Rows[PrjProperty.currentTableIndex].Selected = true;
                }
                catch
                { 
                    //Выпала из списка последняя запись
                }

                try
                {

                    //№
                    dataGridView1.Columns[2].Width = 30;
                    //SDate
                    dataGridView1.Columns[3].Width = 110;
                    //EDate
                    dataGridView1.Columns[4].Width = 110;
                    //Ent
                    dataGridView1.Columns[6].Width = 150;
                    //Emps
                    dataGridView1.Columns[7].Width = 100;

                    //Task
                    dataGridView1.Columns[5].Width = Width - 20 - 630;
                }
                catch
                {
                    //запуск первый раз
                }
            }

        }

        /// <summary>
        /// Первый запуск
        /// </summary>
        private void Form1_Shown(object sender, EventArgs e)
        {
            
            dateTimePickerFilter.Value = DateTime.Now;
            RefreshTable();
            DataGridViewButtonColumn btn = new DataGridViewButtonColumn();
            dataGridView1.Columns.Insert(0, btn);
            dataGridView1.Columns[0].Width = 30;
            btn.Text = "";
            btn.HeaderText = "OK";
            btn.Name = "btn";
            btn.UseColumnTextForButtonValue = true;
            DataGridViewButtonColumn btnEdit = new DataGridViewButtonColumn();
            dataGridView1.Columns.Insert(1, btnEdit);
            dataGridView1.Columns[1].Width = 30;
            btnEdit.Text = "";
            btnEdit.HeaderText = "Edit";
            btnEdit.Name = "btnEdit";
            btnEdit.UseColumnTextForButtonValue = true;

            //№
            dataGridView1.Columns[2].Width = 30;
            //SDate
            dataGridView1.Columns[3].Width = 110;
            //EDate
            dataGridView1.Columns[4].Width = 110;
            //Ent
            dataGridView1.Columns[6].Width = 150;
            //Emps
            dataGridView1.Columns[7].Width = 100;

            //Task
            dataGridView1.Columns[5].Width = Width - 20 - 630;

            comboBoxEmps.DataSource = PrjProperty.listEmps;
            comboBoxEmps.DisplayMember = "Value";
        }

        /// <summary>
        /// [Добавить]
        /// </summary>
        private void buttonAdd_Click(object sender, EventArgs e)
        {
            FormEditZVS formEdit = new FormEditZVS();

            formEdit.semps_id = "";
            formEdit.sent_id = "";
            formEdit.curemps_indx = comboBoxEmps.SelectedIndex;
            formEdit.sdate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            formEdit.textBoxTimeSDate.Text = DateTime.Now.ToString("HH:mm");
            formEdit.edate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            formEdit.textBoxTimeEDate.Text = DateTime.Now.ToString("HH:mm");
            formEdit.checkEditIsEDate.Checked = true;
            formEdit.checkEditIsEDate.Checked = false;

            if (formEdit.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {

                mdbConn.ConnectionString = connString;
                mdbConn.Open();
                string sqlInsert = String.Format("insert into zvs_zayav (sdate,edate,ent_id,emps_id,subject) values({0},{1},{2},{3},\"{4}\")", formEdit.sdate, formEdit.edate, formEdit.sent_id, formEdit.semps_id, formEdit.subject);
                using (OleDbCommand command = new OleDbCommand())
                {
                    command.Connection = mdbConn;
                    command.CommandType = System.Data.CommandType.Text;
                    command.CommandText = sqlInsert;
                    command.ExecuteNonQuery();
                }
                mdbConn.Close();
                RefreshTable();
            }
        }

        /// <summary>
        /// Изменение даты отображения
        /// </summary>
        private void dateTimePickerFilter_ValueChanged(object sender, EventArgs e)
        {
            RefreshTable();
        }

        /// <summary>
        /// Чекбокс - [текущие]
        /// </summary>
        private void checkBoxOnlyCurrent_CheckedChanged(object sender, EventArgs e)
        {
            RefreshTable();
        }

        /// <summary>
        /// [Редактировать]
        /// </summary>
        private void buttonEdit_Click(object sender, EventArgs e)
        {
            FormEditZVS formEdit = new FormEditZVS();
            //ZEvent zevent = null;
            //= dataGridView1.SelectedRows[0].;

            ZEvent zevent = (ZEvent)dataGridView1.CurrentRow.DataBoundItem;
            if (zevent != null)
            {
                formEdit.textBoxTask.Text = zevent.ZVSTask;
                DateTime dt;
                if (DateTime.TryParse(zevent.ZVSSDate, out dt))
                {
                    formEdit.dateTimePickerSDate.Value = dt;
                    formEdit.textBoxTimeSDate.Text = dt.ToString("HH:mm");
                }
                formEdit.checkEditIsEDate.Checked = true;
                if (zevent.ZVSEDate == null)
                {
                    formEdit.checkEditIsEDate.Checked = false;
                }
                if (DateTime.TryParse(zevent.ZVSEDate, out dt))
                {
                    formEdit.dateTimePickerEDate.Value = dt;
                    formEdit.textBoxTimeEDate.Text = dt.ToString("HH:mm");
                }
                else
                    formEdit.checkEditIsEDate.Checked = false;

                formEdit.semps_id = zevent.ZEmpsID;
                formEdit.sent_id = zevent.ZEntID;




                if (formEdit.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {

                    mdbConn.ConnectionString = connString;
                    mdbConn.Open();
                    string sqlInsert = String.Format("update  zvs_zayav  set sdate={0}, edate={1}, ent_id={2}, emps_id={3}, subject=\"{4}\"  where id={5}", formEdit.sdate, formEdit.edate, formEdit.sent_id, formEdit.semps_id, formEdit.subject, zevent.ZVSNO);
                    using (OleDbCommand command = new OleDbCommand())
                    {
                        command.Connection = mdbConn;
                        command.CommandType = System.Data.CommandType.Text;
                        command.CommandText = sqlInsert;
                        command.ExecuteNonQuery();
                    }
                    mdbConn.Close();
                    RefreshTable();
                }
            }
        }

        /// <summary>
        /// Кнопки [ok] [edit] в таблице 
        /// </summary>
        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            #region button OK
            if (dataGridView1.Columns[e.ColumnIndex].HeaderText == "OK")
            {
                ZEvent zevent = (ZEvent)dataGridView1.CurrentRow.DataBoundItem;
                if (zevent != null)
                {
                    mdbConn.ConnectionString = connString;
                    mdbConn.Open();
                    string edate = String.Format("#{0}#", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    string sqlInsert = String.Format("update  zvs_zayav  set edate={0}  where id={1}", edate, zevent.ZVSNO);
                    using (OleDbCommand command = new OleDbCommand())
                    {
                        command.Connection = mdbConn;
                        command.CommandType = System.Data.CommandType.Text;
                        command.CommandText = sqlInsert;
                        command.ExecuteNonQuery();
                    }
                    mdbConn.Close();
                    RefreshTable();
                }
            }
            #endregion
            #region Edit
            if (dataGridView1.Columns[e.ColumnIndex].HeaderText == "Edit")
            {
                buttonEdit_Click(sender, e);
            }
            #endregion
        }

        /// <summary>
        /// Изменение размеров формы
        /// </summary>
        private void Form1_SizeChanged(object sender, EventArgs e)
        {
            //Task
            if (Width - 20 - 585 > 100)
                dataGridView1.Columns[5].Width = Width - 20 - 630;
        }

        /// <summary>
        /// [Обновить]
        /// </summary>
        private void buttonRefresh_Click(object sender, EventArgs e)
        {
            RefreshTable();
        }

        /// <summary>
        /// Отрисовка кнопок [ok] [edit] в таблице
        /// </summary>
        private void dataGridView1_CellPainting(object sender, DataGridViewCellPaintingEventArgs e)
        {
            #region Edit
            if (e.RowIndex >= 0 && (e.ColumnIndex == 1 || e.ColumnIndex == 0))
            {
                e.Paint(e.CellBounds, DataGridViewPaintParts.All);
                if (e.ColumnIndex == 0)
                    e.Graphics.DrawIcon(Properties.Resources.Accept, new Rectangle() { X = e.CellBounds.Left + 3, Y = e.CellBounds.Top + 3, Width = 16, Height = 16 });
                if (e.ColumnIndex == 1)
                    e.Graphics.DrawIcon(Properties.Resources.Edit, new Rectangle() { X = e.CellBounds.Left + 3, Y = e.CellBounds.Top + 3, Width = 16, Height = 16 });

                e.Handled = true;
            }
            #endregion

        }

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            comboBoxEmps.Enabled = checkBox1.Checked;
            RefreshTable();
        }

        private void comboBoxEmps_SelectedIndexChanged(object sender, EventArgs e)
        {
            RefreshTable();
        }

    }
}
